#!/bin/bash

# 配置源数据库和目标数据库的连接信息
SOURCE_HOST="10.10.8.62"
SOURCE_PORT=3306
SOURCE_USER="your_user"
SOURCE_PASSWORD='your_password'

TARGET_HOST="10.10.8.62"
TARGET_PORT=3307
TARGET_USER="your_user"
TARGET_PASSWORD="your_password"

LOG_PATH="/tmp"

# create user your_user@'%' identified by 'your_password'; grant all on *.* to your_user@'%';flush privileges;
# drup user your_user@'%'; 

# 打印使用方法
usage() {
  echo "用法: $0 --sync_type=<all|database|table> [options]"
  echo "参数:"
  printf "  --sync_type=[all|database|table]  # 指定同步类型 (同步整个实例/同步数据库/同步表)\n"
  printf "  --source_database=<源库名>        # 指定源数据库名\n"
  printf "  --target_database=<目标库名>      # 指定目标数据库名\n"
  printf "  --table=<表名>                    # 指定单个表同步或多个表(多个表用 "," 分隔)\n"
  printf "  --table_file=<文件路径>           # 指定列出表名的文件路径 适用于表多的情况\n"
  echo " "
  echo "运行前请安装 sshpass/mysql/mysqldump 命令并指定授权的用户"
  echo "所有用法:"
  echo "# 同步整个实例:"
  echo "  $0 --sync_type=all" 
  echo "# 同步库:"
  echo "  $0 --sync_type=database --source_database=<source_db> [--target_database=<target_db>]"
  echo "# 同步单表或多表:"
  echo "  $0 --sync_type=table    --source_database=<source_db> --table=table1,table2,table3  [--target_database=<target_db>]"
  echo "# 同步文件中的表:"
  echo "  $0 --sync_type=table    --source_database=<source_db> --table_file=./table_file.txt [--target_database=<target_db>]"
  exit 1
}


# 源库连接信息
MYSQL_SOURCE_CONN="sshpass -p "$SOURCE_PASSWORD" mysql -h $SOURCE_HOST -u $SOURCE_USER -p -P $SOURCE_PORT "
MYSQLDUMP_SOURCE_CONN="sshpass -p "$SOURCE_PASSWORD" mysqldump -h $SOURCE_HOST -u $SOURCE_USER -p -P $SOURCE_PORT  -R -E --triggers   --set-gtid-purged=OFF  --single-transaction  "

# 目标库连接信息
MYSQL_TARGET_CONN="sshpass -p "$TARGET_PASSWORD" mysql -h $TARGET_HOST -u $TARGET_USER -p -P $TARGET_PORT "

LOG_FILE="${LOG_PATH}/data_sync_$(date '+%Y%m%d%H%M%S').log"
MYSQL_LOG_FILE="${LOG_PATH}/data_sync_mysql_$(date '+%Y%m%d%H%M%S').log"

# 日志消息
log_message() {
    local message_content="$1"
    local message_status="$2"
    local timestamp="$(date '+%Y-%m-%d %H:%M:%S')"

    printf "%-s  %-6s %-s\n" "$timestamp" "$message_status" "$message_content" >>${LOG_FILE}
    printf "%-s  %-6s %-s\n" "$timestamp" "$message_status" "$message_content"
}

# 检查连接
check_conn(){
    local status=0
    $MYSQL_SOURCE_CONN -e 'select 1' &>/dev/null
    if [ $? -ne 0 ];then
        log_message "MySQL 源库连接失败" "err"
        status=1
    fi

    $MYSQL_TARGET_CONN -e 'select 1' &>/dev/null
    if [ $? -ne 0 ];then
        log_message "MySQL 目标库连接失败" "err"
        status=1
    fi

    if [ ${status} -ne 0 ];then
        exit 1
    fi
}

# 检查源库是否存在
check_database_exists(){
    local db=$1
    local instance=$2
    local MySQL_CONN=""

    case $instance in
    source)
        MySQL_CONN="$MYSQL_SOURCE_CONN"
        ;;
    target)
        MySQL_CONN="$MYSQL_TARGET_CONN"
        ;;
    *)
      log_message "输入错误" "err"
    esac

    local result=$($MySQL_CONN -N -e "SHOW DATABASES LIKE '$db'" 2>/dev/null)
    local status=$?
    if [[ "$result" != "$db" || $status -ne 0  ]]; then
        echo 1
    else
        echo 0
    fi

}

# 检查数据库是否为空
check_database_is_null(){
    local db=$1
    local result=$($MYSQL_TARGET_CONN $db -N -e "SHOW tables" 2>/dev/null)
    local status=$?
    if [[ -n "$result" || $status -ne 0 ]]; then
      echo 1 # 不存在表
    else
      echo 0 # 存在表
    fi
}

# 检查源库表是否存在
check_table_exists_source() {
    local db=$1
    local table=$2
    local result=$($MYSQL_SOURCE_CONN -N -e "SHOW TABLES FROM $db LIKE '$table'" 2>/dev/null)
    local status=$?
    if [[ -z "$result" || $status -ne 0 ]]; then
        echo 1
    else
        echo 0
    fi
}

# 检查目标库表是否存在
check_table_exists_target() {
    local db=$1
    local table=$2
    local result=$($MYSQL_TARGET_CONN -N -e "SHOW TABLES FROM $db LIKE '$table'" 2>/dev/null)
    local status=$?
    if [[ -n "$result" || $status -ne 0 ]]; then
        echo 1
    else
        echo 0
    fi
}

# 同步单个表
sync_table() {
    local source_db=$1
    local target_db=$2
    local table=$3

    log_message "${SOURCE_HOST}:${SOURCE_PORT} ${source_db}.${table} ===> ${TARGET_HOST}:${TARGET_PORT} ${target_db}.${table} 同步中···" "info"

    $MYSQLDUMP_SOURCE_CONN $source_db $table 2>>${MYSQL_LOG_FILE} |grep -v "USE \`${source_db}\`;" | $MYSQL_TARGET_CONN $target_db 2>>${MYSQL_LOG_FILE}
    if [[ ${PIPESTATUS[0]} -eq 0 && ${PIPESTATUS[1]} -eq 0 && ${PIPESTATUS[2]} -eq 0 ]]; then
        log_message "${SOURCE_HOST}:${SOURCE_PORT} ${source_db}.${table} ===> ${TARGET_HOST}:${TARGET_PORT} ${target_db}.${table} 同步完成!" "info"
    else
        log_message "${SOURCE_HOST}:${SOURCE_PORT} ${source_db}.${table} ===> ${TARGET_HOST}:${TARGET_PORT} ${target_db}.${table} 同步失败!" "err"
        exit 1
    fi
}

# 同步多个表
sync_multi_tables(){
    local source_db=$1
    local target_db=$2
    local table=$3
    local table_status=0
    
    # 检查库是否存在
    local status=$(check_database_exists $source_db source)
    if [ $status  -ne 0 ];then
        log_message "源  库 ${SOURCE_HOST}:${SOURCE_PORT}-${source_db} 不存在" "err"
        exit 1
    fi
    
    # 检查目标库是否存在
    status=$(check_database_exists $target_db target)
    if [ $status  -ne 0 ];then
        log_message "目标库 ${TARGET_HOST}:${TARGET_PORT}-${target_db} 不存在" "err"
        exit 1
    fi

    # 读取表到数组
    IFS=',' read -ra TABLE_ARRAY <<< "$table"

    # 循环数组中的表名
    for table_name in "${TABLE_ARRAY[@]}"; do
      # 检查源库, 表是否存在
      status=$(check_table_exists_source $source_db  $table_name)
      if [ $status -ne 0 ];then
          log_message "源  表 ${SOURCE_HOST}:${SOURCE_PORT}-${source_db}.${table_name} 不存在" "err"
          table_status=1
      fi

      # 检查目标库, 表是否存在
      status=$(check_table_exists_target $target_db  $table_name)
      if [ $status -ne 0 ];then
          log_message "目标表 ${TARGET_HOST}:${TARGET_PORT}-${target_db}.${table_name} 已存在" "err"
          table_status=1
      fi
    done

    # 检查完成后 获取状态 有异常则退出
    if [ $table_status -ne 0 ];then
      exit 1
    fi
    
    # 执行同步函数
    for table_name in "${TABLE_ARRAY[@]}"; do
      sync_table $source_db $target_db $table_name
    done
    
}

# 从文件读取表名并同步
sync_tables_from_file(){
    local source_db=$1
    local target_db=$2
    local file=$3
    local table_status=0
    
    local status=$(check_database_exists $source_db source)
    if [ $status  -ne 0 ];then
        log_message "源  库 ${SOURCE_HOST}:${SOURCE_PORT}-${source_db} 不存在" "err"
        exit 1
    fi

    status=$(check_database_exists $target_db target)
    if [ $status  -ne 0 ];then
        log_message "目标库 ${TARGET_HOST}:${TARGET_PORT}-${target_db} 不存在" "err"
        exit 1
    fi

    
    while IFS= read -r table_name; do
      if [[ $table_name =~ ^\s*# || $table_name =~ ^\s*/ || $table_name =~ ^\s*$ ]]; then
        continue
      fi

      # 检查源库, 表是否存在
      status=$(check_table_exists_source $source_db  $table_name)
      if [ $status -ne 0 ];then
          log_message "源  表 ${SOURCE_HOST}:${SOURCE_PORT}-${source_db}.${table_name} 不存在" "err"
          table_status=1
      fi

      # 检查目标库, 表是否存在
      status=$(check_table_exists_target $target_db  $table_name)
      if [ $status -ne 0 ];then
          log_message "目标表 ${TARGET_HOST}:${TARGET_PORT}-${target_db}.${table_name} 已存在" "err"
          table_status=1
      fi
    done < $file

    # 检查完成后 获取状态 有异常则退出
    if [ $table_status -ne 0 ];then
      exit 1
    fi

    while IFS= read -r table_name; do
      if [[ $table_name =~ ^\s*# || $table_name =~ ^\s*/ || $table_name =~ ^\s*$ ]]; then
        continue
      fi
      sync_table $source_db $target_db $table_name
    done < $file
}


# 同步整个数据库
sync_database() {
    local source_db=$1
    local target_db=$2

    local status=$(check_database_exists $source_db source)
    if [ $status  -ne 0 ];then
        log_message "源库 ${SOURCE_HOST}:${SOURCE_PORT}-${source_db} 不存在" "err"
        exit 1
    fi

    local status=$(check_database_exists $target_db target)
    if [ $status  -ne 0 ];then
        log_message "目标库 ${TARGET_HOST}:${TARGET_PORT}-${target_db} 不存在" "err"
        exit 1
    fi
    
    local status=$(check_database_is_null $target_db)
    if [ $status  -ne 0 ];then
        log_message "目标库 ${TARGET_HOST}:${TARGET_PORT}-${target_db} 存在表" "err"
        exit 1
    fi

    log_message "${SOURCE_HOST}:${SOURCE_PORT}-${source_db} ===> ${TARGET_HOST}:${TARGET_PORT}-${target_db} 同步中···" "info"

    $MYSQLDUMP_SOURCE_CONN -B $source_db  2>${MYSQL_LOG_FILE}|grep -v "USE \`${source_db}\`;" |  $MYSQL_TARGET_CONN $target_db  2>${MYSQL_LOG_FILE}
    if [[ ${PIPESTATUS[0]} -eq 0 && ${PIPESTATUS[1]} -eq 0 && ${PIPESTATUS[2]} -eq 0 ]]; then
        log_message "${SOURCE_HOST}:${SOURCE_PORT}-${source_db} ===> ${TARGET_HOST}:${TARGET_PORT}-${target_db} 同步成功!" "info"
    else
        log_message "${SOURCE_HOST}:${SOURCE_PORT}-${source_db} ===> ${TARGET_HOST}:${TARGET_PORT}-${target_db} 同步失败!" "err"
        exit 1
    fi
}

# 同步整个实例
sync_instance(){
  $MYSQLDUMP_SOURCE_CONN -A 2>${MYSQL_LOG_FILE} |$MYSQL_TARGET_CONN 2>${MYSQL_LOG_FILE}
  if [[ ${PIPESTATUS[0]} -eq 0 && ${PIPESTATUS[1]} -eq 0  ]]; then
      log_message "实例同步成功" "info"
  else
      log_message "实例同步失败" "err"
      exit 1
  fi
}

# 初始化变量
sync_type=""
source_database=""
target_database=""
table=""
table_file=""

# 解析命令行参数
for arg in "$@";do
  case $arg in
    --sync_type=*)
      sync_type="${arg#*=}"
      ;;
    --source_database=*)
      source_database="${arg#*=}"
      ;;
    --target_database=*)
      target_database="${arg#*=}"
      ;;
    --table=*)
      table="${arg#*=}"
      ;;
    --table_file=*)
      table_file="${arg#*=}"
      ;;
    *)
      log_message "Unknown option: $arg" "err"
      usage
      ;;
  esac
done

# 检查并执行对应的同步操作
case $sync_type in
  all)
    log_message "开始同步全量数据" "info"
    # 调用同步整个实例的函数
    sync_instance
    ;;
  database)
    if [ -z "$source_database" ]; then
      log_message "--source_database 参数是必须的" "err"
      usage
    fi

    if [ -z "$target_database" ]; then
      target_database=$source_database
    fi
    log_message "开始同步单库数据" "info"
    # 调用同步单个库的函数
    sync_database $source_database $target_database
    ;;
  table)
    if [ -z "$source_database" ]; then
      log_message "--source_database 参数是必须的" "err"
      usage
    fi

    if [ -n "$table" ]; then
      # 目标库名不写时 默认等于源库名 
      if [ -z "$target_database" ]; then
        target_database=$source_database
      fi
      log_message "开始同步表数据" "info"
      # 多表同步
      sync_multi_tables $source_database $target_database $table

    elif [ -n "$table_file" ]; then
      egrep -v '^\s*#|^\s*//|^\s*$' $table_file |sort |uniq |sed 's#`##g' >${table_file}.ghost
      table_file=${table_file}.ghost

      #  目标库名不写时 默认等于源库名
      if [ -z "$target_database" ]; then
        target_database=$source_database
      fi
      log_message "开始同步表文件数据" "info"
      # 读取文件中的表备份
      sync_tables_from_file $source_database $target_database $table_file

    else
      log_message "--table 或 --table_file 参数是必须的" "err"
      usage
    fi
    ;;
  *)
    log_message "无效的 --sync_type 参数: $sync_type" "err"
    usage
    ;;
esac

log_message "数据同步已完成! bye!" "info"
exit 0

